package cn.usersmsg.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class BaseDB {
	/**
	 * 数据库链接操作
	 */
	
	//mysql
	static private final String DRIVER="com.mysql.jdbc.Driver";
	static private final String URL="jdbc:mysql://172.16.23.123:3306/test";
	static private final String ROOT="root";
	static private final String PASSWORD="1234";
	
	//Oracle
/*	private final static String DRIVER="oracle.jdbc.driver.OracleDriver";
	private final static String URL = "jdbc:oracle:thin:@172.16.23.123:1521:orcl";//SID链接
//	private final static String URL = "jdbc:oracle:thin:@172.16.23.191:1521:orcl";//SID链接
//	private final static String URL = "jdbc:oracle:thin:@//172.16.23.191:1521/orcl.16.23.191";//server_name链接
	private final static String ROOT = "scott";
	private final static String PASSWORD="1234";*/
	@Test
	public void test(){
		getConn();
	}
	/**
	 * 获取Connection链接对象
	 * @return Connection
	 */
	public static Connection getConn(){
		Connection conn = null ;
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, ROOT, PASSWORD);
//			System.out.println("数据库链接");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch(Throwable t){
			t.printStackTrace();
		} 
		return conn;		
	}
	/**
	 * 数据库链接释放
	 * @param conn
	 * @param st
	 * @param rs
	 */
	public static void close(Connection conn, Statement st, ResultSet rs){
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (st!=null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 数据库的查询并返回结果集
	 * @param sql语句
	 * @return ResultSet
	 */
	public static ResultSet select(String sql){
		Connection conn = null; 
		ResultSet rs = null;	
		Statement st =null;
		try {
			conn = getConn();
			st = conn.createStatement();
			rs = st.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return rs;		
	}

	//@Test
	public void testselect(){
		ResultSet rs = update("drop table test");
		try {
			if (rs.next()) {
				System.out.println(rs.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 数据库增删改操作
	 * @param sql
	 * @return ResultSet返回添加数据时 主键的结果集
	 */
	public static ResultSet update(String sql){
		Connection conn = null;
		Statement st =null;
		ResultSet rs =null;
		conn=getConn();
		try {
			st =conn.createStatement();
			int a =st.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
			if(a>=0)
				System.out.println("操作成功"+a+"条数据受影响");				
			else
				System.out.println("操作失败");
			rs = st.getGeneratedKeys();		
		} catch (SQLException e) {
			System.out.println("操作出错");
		}	
		return rs;
	}	
	
	/**
	 * MySql表导出txt文件
	 * @param tbname
	 * @param filename
	 */
	public static void outfile(String tbname,String filename){
		update("select * into outfile '"+filename+"' lines terminated by '/r/n' from "+tbname);
		System.out.println("导出成功!文件路径:"+filename);
		//导出到数据到windows文本文件时，为了方便查看，也需要相同的设置 
		//mysql> select * from tt into outfile 'd.txt'
		//-> fields terminated by','
		//-> lines terminated by'\r\n'
	}
	/**
	 * MySql表导入txt文件
	 * @param tbname
	 * @param filename
	 */
	public static void infile(String tbname,String filename){
		update("load data local infile '"+filename+"' into table "+tbname+" lines terminated by '/r/n'");
		System.out.println("导入成功!");
		/*mysql> load data infile 'd.txt' into table tt
	    -> fields terminated by','
	    -> lines terminated by'\r\n'	 
		 注意的地方：
		 1）、文件可以使用绝对路径如'c:/d.txt'，否则请将文件放在数据库根目录中
		 2）、因为字段之间用了逗号隔开，所以必须fields terminated by','，否则导入失败
		 3）、因为Winsows中行以“\r\n”隔开，所以必须lines terminated by'\r\n'，
		 如果不设置这个参数，也可导入成功，但是会多导入一个“\r”控制字符，可能在可视化
		 MySQL工具中看不出字段异样，但是在MySQL命令行中显示会明显混乱。
		 4）、如果表tt非空，且文件中的ind值在表中有重复，会提示错误，并导入失败。		 
		 只导入name字段，文件d.txt的内容:
		 a
		 b
		 c		 
		 mysql> load data infile 'd.txt' into table tt
		    -> lines terminated by'\r\n'
		    -> (name);		 
		 load data 命令还支持更复杂的文本格式、文件编码等，可参考官方文档。*/
	}
	
	/**
	 * 修改单项属性值
	 * @param dbName 库名
	 * @param tbName 表明
	 * @param up 要修改的属性 
	 * @param upvalue 要修改的属性的值 int 
	 * @param REF 参考属性 
	 * @param REFvalue 参考属性的值 String
	 * @return ResultSet 受影响行的主键集合
	 */
	public static ResultSet updateSingleValue(String tbName,String cloumn,Object cloumnvalue,String REF,Object REFvalue){
		Connection conn = null;
		Statement st =null;
		conn=getConn();
		ResultSet rs = null;
		try {
			st =conn.createStatement();
			int a =st.executeUpdate("update "+tbName+" set "+cloumn+"='"+cloumnvalue+"' where "+REF+"="+REFvalue,Statement.RETURN_GENERATED_KEYS);
			rs = st.getGeneratedKeys();			
			if(a>0)
				System.out.println("操作成功");
			else
				System.out.println("操作失败");
		} catch (SQLException e) {
			System.out.println("操作失败");
		} 
		return rs;
	}
	/**
	 * 调用 无出参 的存储过程
	 * @param pro_name 存储过程名
	 * @param obj[] 参数集合 无参则null
	 * @return
	 */
	public static ResultSet callProcedure(String pro_name,Object [] obj){
		Connection conn = null;		
		CallableStatement cs = null;
		String z ="";
		ResultSet rs = null;
		try {
			conn = getConn();
			if (obj==null) {
				cs=conn.prepareCall("{call "+pro_name+"()}");
				cs.execute();
			}else{
				for (int i = 0; i < obj.length; i++) {					
					if (i!=obj.length-1) {
						z+="?,";
					}else
						z+="?";
				}
				cs=conn.prepareCall("{call "+pro_name+"("+z+")}");
				for (int i = 0; i < obj.length; i++) {
					cs.setObject(i+1, obj[i]);
				}
				boolean b = cs.execute();
				if (b) {
					rs = cs.getResultSet();
				}
			}
			System.out.println("存储过程"+pro_name+"调用成功");
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return rs;
	}
	/**
	 * 通用增删改
	 * @param sql
	 * @param obj
	 * @return int 受影响数据行数
	 */
	public static int executeUpdate(String sql,Object [] obj){
		Connection conn = getConn();
		PreparedStatement ps =null;
		int n = 0 ;
		try {
			ps = conn.prepareStatement(sql);
			if (obj!=null) {
				for (int j = 0; j < obj.length; j++) {
					ps.setObject(j+1, obj[j]);
				}				
			}
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return n;		
	}
	/**
	 * 通用查询方法
	 * @param sql
	 * @param obj
	 * @return ResultSet
	 */
	public static ResultSet executeQuery(String sql,Object [] obj){
		Connection conn = getConn();
		PreparedStatement ps =null;
		ResultSet rs =null;
		try {
			ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			if (obj!=null) {
				for (int j = 0; j < obj.length; j++) {
					ps.setObject(j+1, obj[j]);
				}				
			}
			rs = ps.executeQuery();
/*			ResultSetMetaData rsd = rs.getMetaData();
			int colCount = rsd.getColumnCount();
			while(rs.next()){
				for (int i = 0; i < colCount; i++) {
					System.out.print(rs.getObject(i+1)+"\t");
				}
				System.out.println();
			}*/
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return rs;	
	}
}
